__version__ = "1.1.0"
import logging

import pymysql

from Tools.logtools.logtools import logfile_hander, console_handler

logger = logging.getLogger(__name__)
logger.setLevel(level=logging.INFO)
logger.addHandler(logfile_hander())
logger.addHandler(console_handler())


class MysqlTool:
    def __init__(self, ip, port, database, user, pwd):
        super().__init__()
        self.connection = None
        self.ip = ip
        self.port = port
        self.database = database
        self.user = user
        self.pwd = pwd

    # 查询第一条数据
    def query_one(self, sql):
        self.connection = pymysql.connect(host=self.ip, user=self.user, password=self.pwd, database=self.database,
            port=self.port, cursorclass=pymysql.cursors.DictCursor, )
        with self.connection:
            with self.connection.cursor() as cursor:
                # Read a single record
                # sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
                # cursor.execute(sql, ('webmaster@python.org',))
                cursor.execute(sql)
                logger.info(sql)
                result = cursor.fetchone()
                logger.info(result)
        return result

    #shp_2024_11_25 新建查询所有数据
    def query_all(self, sql):
        self.connection = pymysql.connect(host=self.ip, user=self.user, password=self.pwd, database=self.database,
            port=self.port, cursorclass=pymysql.cursors.DictCursor, )
        with self.connection:
            with self.connection.cursor() as cursor:
                query = cursor.execute(sql)
                logger.info('数据总数: '+str(query))
                logger.info(sql)
                result = cursor.fetchall()
        return result

    # 执行sql文件
    def mysql_script(self, sql_file=None):
        self.connection = pymysql.connect(host=self.ip, user=self.user, password=self.pwd, database=self.database,
            port=self.port, cursorclass=pymysql.cursors.DictCursor, )
        with open(sql_file, "r") as f:  # 打开文件
            data = f.read()
            lines = data.splitlines()
            sql_data = ""
            # 将--注释开头的全部过滤，将空白行过滤
            for line in lines:
                if len(line) == 0:
                    continue
                elif line.startswith("--"):
                    continue
                else:
                    sql_data += line
            sql_list = sql_data.split(";")[:-1]
            sql_list = [x.replace("\n", " ") if "\n" in x else x for x in sql_list]
            logger.info("------------------- SQL ------------------------")
            logger.info(sql_list)

        with self.connection:
            with self.connection.cursor() as cursor:
                for sql in sql_list:
                    cursor.execute(sql)
            self.connection.commit()
        logger.info("--------------------------------------------------")

    def searchtable(self, endfix):
        """
SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE 'Name';
SELECT column_name from information_schema.columns WHERE column_name LIKE ’%searchTerm%’ AND table_schema = ‘yourDB’
SELECT column_name from information_schema.columns WHERE column_name LIKE ’%searchTerm%’ AND table_schema = ‘yourDB’ AND table_name = ‘yourDBTable’
        :return:
        """
        sql = "SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%" + endfix + "%';"
        self.query_one(sql)

    def drop_split_table(self):
        self.connection = pymysql.connect(host=self.ip, user=self.user, password=self.pwd, database=self.database,
            port=self.port, cursorclass=pymysql.cursors.DictCursor, )

        with self.connection:
            with self.connection.cursor() as cursor:
                # Read a single record
                # sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
                # cursor.execute(sql, ('webmaster@python.org',))
                cursor.execute("select SPLIT_TABLE_NAME from nrlj_big_table_split_info")
                for line in cursor.fetchall():
                    table_name = line['SPLIT_TABLE_NAME']
                    logger.info("DROP TABLE ", table_name)
                    sql = sql + "DROP TABLE " + table_name + ";\n"
                    cursor.execute(sql)


if __name__ == "__main__":
    # danji_ip = "172.31.157.2"
    danji_ip = "192.168.20.20"
    neirongdb = "neirongdb"
    neirongdb_user = "nruser"
    neirongdb_pwd = "nrlj2023"
    neirongdb_port = 3306
    db = MysqlTool(danji_ip, database=neirongdb, user=neirongdb_user, pwd=neirongdb_pwd, port=neirongdb_port, )
    db.query_all(
         "select * from nrlj_detail_phone_message ndpm "
    )
    # db.query_one(
    #     "SELECT id_num FROM nrlj_phone_manage where phone_path='/home/model_code/data/make/bcp/"
    #     "137-705420347-650102-650102-1712556388-00001'"
    # )
    db.drop_split_table()
